package com.central.apps.controller;

import cn.hutool.core.util.IdUtil;
import com.central.app.common.base.BaseHandler;
import com.central.app.common.cache.CfgRedisCache;
import com.central.app.common.constant.AppConstant;
import com.central.app.common.util.AppCommonUtil;
import com.central.app.handler.factory.HandlerFactory;
import com.central.app.model.BizApp;
import com.central.app.model.BizObject;
import com.central.app.model.CommonModel;
import com.central.app.service.HandlerService;
import com.central.apps.common.constant.InventoryConstant;
import com.central.common.auth.details.LoginAppUser;
import com.central.common.exception.service.ServiceException;
import com.central.common.util.SysUserUtil;
import com.central.common.util.ToolUtil;
import com.central.common.web.PageResult;
import com.central.common.web.Result;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.*;

/**
 * @Author mengweitao
 * @Date 2022/11/30 12:10
 * 物料主数据导入导出，绵阳模板数据导出
 */
@Log4j2
@RestController
@RequestMapping("/materialMasterData")
public class MaterialMasterDataController {

    private final static String XLS = "xls";
    private final static String XLSX = "xlsx";

    @Autowired
    private HandlerService handlerService;
    @Autowired
    private CfgRedisCache cfgRedisCache;

    /**
     * 物料主数据导出
     *
     * @param response
     * @throws IOException
     */
    @PostMapping("/materialMasterDataExportExcel")
    public void materialMasterDataExportExcel(HttpServletResponse response) throws IOException {

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("Sheet1");
        HSSFRow row = sheet.createRow(0);

        HSSFFont redFont = wb.createFont();
        redFont.setColor(Font.COLOR_RED);
        HSSFCellStyle style = wb.createCellStyle();
        style.setFont(redFont);
//        style.setWrapText(true);
//        style.setAlignment(HorizontalAlignment.LEFT);// 左右居中
//        style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中

        HSSFCellStyle style1 = wb.createCellStyle();
        HSSFDataFormat format = wb.createDataFormat();
        style1.setDataFormat(format.getFormat("@"));
        sheet.setDefaultColumnStyle(7, style1);
        sheet.setDefaultColumnStyle(8, style1);

        //给第一行里面添数据
        HSSFCell cell1 = row.createCell(0);
        cell1.setCellStyle(style);
        cell1.setCellValue("物料名称");
//        HSSFCell cell2 = row.createCell(1);
//        cell2.setCellStyle(style);
//        cell2.setCellValue("启用状态");
        row.createCell(1).setCellValue("原物料编码");
        row.createCell(2).setCellValue("等级");
        row.createCell(3).setCellValue("有效期（月）");
        row.createCell(4).setCellValue("规格");
        HSSFCell cell3 = row.createCell(5);
        cell3.setCellStyle(style);
        cell3.setCellValue("型号");
        HSSFCell cell4 = row.createCell(6);
        cell4.setCellStyle(style);
        cell4.setCellValue("物料大类编码");
        HSSFCell cell5 = row.createCell(7);
        cell5.setCellStyle(style);
        cell5.setCellValue("物料中类编码");
        HSSFCell cell6 = row.createCell(8);
        cell6.setCellStyle(style);
        cell6.setCellValue("物料小类编码");
        HSSFCell cell7 = row.createCell(9);
        cell7.setCellStyle(style);
        cell7.setCellValue("计量单位");
        HSSFCell cell = row.createCell(10);
        cell.setCellStyle(style);
        cell.setCellValue("主要技术指标");
        HSSFCell cell9 = row.createCell(11);
        cell9.setCellStyle(style);
        cell9.setCellValue("品牌");
        HSSFCell cell8 = row.createCell(12);
//        cell8.setCellStyle(style);
        cell8.setCellValue("封装形式");
        row.createCell(13).setCellValue("供应商名称");
        row.createCell(14).setCellValue("检验标准");
        HSSFCell cell10 = row.createCell(15);
        cell10.setCellStyle(style);
        cell10.setCellValue("生产厂家");
//        row.createCell(16).setCellValue("生产厂家");
        row.createCell(16).setCellValue("材料牌号");
        row.createCell(17).setCellValue("表面处理");
        row.createCell(18).setCellValue("执行/技术/标准号");
//        row.createCell(19).setCellValue("物料分类");
//        row.createCell(20).setCellValue("物料税类");
        row.createCell(19).setCellValue("版本号");
//        row.createCell(22).setCellValue("税率");
//        HSSFCell cell11 = row.createCell(20);
//        cell11.setCellStyle(style);
//        cell11.setCellValue("成本类别");
        row.createCell(20).setCellValue("安全库余量");
        row.createCell(21).setCellValue("最低库余量");

        //下拉框配置
//        String zldj = "ZLDJ";  //质量等级
//        Map<String, String> zldjMap = new HashMap<>();
//        zldjMap.put("ZLDJ", "国家鲁班奖,优良,合格,不合格");
//        HSSFDataValidation dataValidation1 = createBox(zldj, zldjMap, 1, 10000, 2, 2);
//        if (dataValidation1 != null) {
//            sheet.addValidationData(dataValidation1);
//        }
//        String zt = "ZT";  //状态
//        Map<String, String> ztMap = new HashMap<>();
//        ztMap.put("ZT", "活动,不活动,草稿");
//        HSSFDataValidation dataValidation2 = createBox(zt, ztMap, 1, 10000, 1, 1);
//        if (dataValidation1 != null) {
//            sheet.addValidationData(dataValidation2);
//        }

        //成本类别
//        String cblb="CBLB";
//        Map<String, String> cblbMap = new HashMap<>();
//        StringJoiner stringJoiner = new StringJoiner(",");
//        List<Map<String, Object>> bySql = handlerService.findBySql("select itemdesc from gq_procostcategory");
//        if(CollectionUtils.isNotEmpty(bySql)){
//            for (Map<String, Object> map : bySql) {
//                String itemdesc = MapUtils.getString(map, "itemdesc");
//                stringJoiner.add(itemdesc);
//            }
//            cblbMap.put("CBLB",stringJoiner.toString());
//            HSSFDataValidation dataValidation3 = createBox(cblb, cblbMap, 1, 10000, 20, 20);
//            if (dataValidation3 != null) {
//                sheet.addValidationData(dataValidation3);
//            }
//        }


        //输出Excel文件
        OutputStream output = response.getOutputStream();
        response.reset();
        String exportFileName = "物料主数据导入模板.xls";
        String fileNameURL = URLEncoder.encode(exportFileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileNameURL + ";" + "filename*=utf-8''" + fileNameURL);
        response.setContentType("application/x-xls");
        wb.write(output);
        output.close();
    }

    @PostMapping("/materialMasterDataImportExcel")
    @Transactional(rollbackFor = Exception.class)
    public Result materialMasterDataImportExcel(@RequestParam("file") MultipartFile myfile) throws Exception {
        try {
            Workbook workbook = null;
            String fileName = myfile.getOriginalFilename();
            if (fileName.endsWith(XLS)) {//获取文件后缀
                workbook = new HSSFWorkbook(myfile.getInputStream());
            } else if (fileName.endsWith(XLSX)) {
                workbook = new XSSFWorkbook(myfile.getInputStream());
            } else {
                throw new Exception("文件不是excel文件");
            }

            //获取excel的Sheet页
            Sheet sheet1 = workbook.getSheet("Sheet1");
            //判断excel一共多少行
            int rows = sheet1.getLastRowNum();

            List<Map<String, Object>> resultList = new ArrayList<>();
            for (int i = 0; i <= rows; i++) {
                Map<String, Object> map = new HashMap<>();
                //读取左上端单元格
                Row row = sheet1.getRow(i);
                //行不为空
                if (row != null) {
                    String description = getCellValue(row, 0).toString().trim();//物料名称
//                    String status = getCellValue(row, 1).toString().trim();//启用状态
                    String rawmatecode = getCellValue(row, 1).toString().trim();//原物料编码
                    String qualitygrade = getCellValue(row, 2).toString().trim();//质量/机械性能/精度
                    String lifemoney = getCellValue(row, 3).toString().trim();//有效期（月）
                    String specifications = getCellValue(row, 4).toString().trim();//规格
                    String model = getCellValue(row, 5).toString().trim();//型号
                    String maxnum = getCellValue(row, 6).toString().trim();//物料大类编码
                    String mediumnum = getCellValue(row, 7).toString().trim();//物料中类编码
                    String smallnum = getCellValue(row, 8).toString().trim();//物料小类编码
                    String unit = getCellValue(row, 9).toString().trim();//计量单位
                    String maintechtors = getCellValue(row, 10).toString().trim();//主要技术指标
                    String spemodel = getCellValue(row, 11).toString().trim();//品牌
                    String packform = getCellValue(row, 12).toString().trim();//封装形式
                    String supplier = getCellValue(row, 13).toString().trim();//供应商名称
                    String insstandard = getCellValue(row, 14).toString().trim();//检验标准
                    String manufacturer = getCellValue(row, 15).toString().trim();//生产厂家
                    String matergrade = getCellValue(row, 16).toString().trim();//材料牌号
                    String surfacement = getCellValue(row, 17).toString().trim();//表面处理
                    String stexecunical = getCellValue(row, 18).toString().trim();//执行/技术/标准号
//                    String materialtion = getCellValue(row, 19).toString().trim();//物料分类
//                    String matetegory = getCellValue(row, 20).toString().trim();//物料税类
                    String versionNo = getCellValue(row, 19).toString().trim();//版本号
//                    String taxrate = getCellValue(row, 22).toString().trim();//税率
//                    String pccdesc = getCellValue(row, 20).toString().trim();//成本类别
                    String securitystock = getCellValue(row, 20).toString().trim();//安全库余量
                    String loweststock = getCellValue(row, 21).toString().trim();//最低库余量
                    //判断模板是否正确
                    if (i == 0) {
                        if (!"物料名称".equals(description) || !"原物料编码".equals(rawmatecode)
                                || !"质量/机械性能/精度".equals(qualitygrade) || !"有效期（月）".equals(lifemoney) || !"规格".equals(specifications) || !"型号".equals(model)
                                || !"物料大类编码".equals(maxnum) || !"物料中类编码".equals(mediumnum) || !"物料小类编码".equals(smallnum) || !"计量单位".equals(unit)
                                || !"主要技术指标".equals(maintechtors) || !"品牌".equals(spemodel) || !"封装形式".equals(packform) || !"供应商名称".equals(supplier)
                                || !"检验标准".equals(insstandard) || !"生产厂家".equals(manufacturer) || !"材料牌号".equals(matergrade) || !"表面处理".equals(surfacement)
                                || !"执行/技术/标准号".equals(stexecunical) || !"版本号".equals(versionNo)
                                || !"安全库余量".equals(securitystock) || !"最低库余量".equals(loweststock)) {
//                            return Result.failed("导入的模板错误，请下载模板进行导入");
                            throw new ServiceException("导入的模板错误，请下载模板进行导入");
                        }
                    } else {
                        //检验必填项
                        int line = i + 1;
                        if (StringUtils.isBlank(description) || StringUtils.isBlank(model) || StringUtils.isBlank(maxnum)
                                || StringUtils.isBlank(mediumnum) || StringUtils.isBlank(smallnum) || StringUtils.isBlank(unit) || StringUtils.isBlank(maintechtors)
                                || StringUtils.isBlank(spemodel) || StringUtils.isBlank(manufacturer)) {

                            throw new ServiceException("第" + line + "行有必填字段未填写。填写后重新导入");
                        }


                        String sql = "select * from gq_item where model ='" + model + "' and description = '" + description + "'";
                        String sqlAdd = " and specifications = '" + specifications + "'";
                        if (StringUtils.isNotBlank(specifications)) {
                            List<Map<String, Object>> bySql = handlerService.findBySql(sql + sqlAdd);
                            if (CollectionUtils.isNotEmpty(bySql)) {
                                throw new ServiceException("第" + line + "行的物料名称为" + description + ",型号为" + model + ",规格为" + specifications + "的物料主数据已存在，请修改后再导入。");
                            }
                        } else {
                            List<Map<String, Object>> res = handlerService.findBySql(sql);
                            if (CollectionUtils.isNotEmpty(res)) {
                                throw new ServiceException("第" + line + "行的物料名称为" + description + ",型号为" + model + "的物料主数据已存在，请修改后再导入。");
                            }
                        }

                        Integer itemnumDefault = InventoryConstant.ITEMNUM_INIT_IDX;
                        StringBuilder stringBuilder = new StringBuilder();
                        String num = "CD" + maxnum + mediumnum + smallnum;
                        stringBuilder.append(num);
                        List<Map<String, Object>> bySql = handlerService.findBySql("select SUBSTRING(itemnum,8) as itemnum from gq_item where itemnum like '%" + num + "%' order by SUBSTRING(itemnum,8) desc limit 1");
                        if (CollectionUtils.isNotEmpty(bySql)) {
                            Map<String, Object> map1 = bySql.get(0);
                            String itemnum1 = MapUtils.getString(map1, "itemnum");//后6位流水号
                            int itemnum1New = Integer.parseInt(itemnum1) + 1;
                            stringBuilder.append(itemnum1New);
                        } else {
                            stringBuilder.append(itemnumDefault);
                        }

//                        //物料编码不能重复导入  物料名称+型号  不能重复
//                        List<Map<String, Object>> bySql7 = handlerService.findBySql("select count(0) as count from gq_item where model ='" + model + "' and description = '" + description + "'");
//                        if(MapUtils.getInteger(bySql7.get(0),"count")>0){
//                            int lineNew = i+1;
//                            throw new ServiceException("第"+lineNew+"行的物料名称，规格型号的物料主数据已存在或者重复导入，请检查后再导入");
//                        }
////                        List<Map<String, Object>> bySql5 = handlerService.findBySql("select count(0) as count from gq_item where itemnum = '" + stringBuilder.toString() + "'");
////                        if(MapUtils.getInteger(bySql5.get(0),"count")>0){
////                            throw new ServiceException("第"+line+"行生成的物料编码已存在或存在重复导入，请检查后再导入");
////                        }
                        map.put("itemnum", stringBuilder.toString());
                        map.put("description", description);//物料名称
                        map.put("status", "已启用");
                        map.put("rawmatecode", rawmatecode);
                        map.put("qualitygrade", qualitygrade);
                        map.put("lifemoney", lifemoney);
                        map.put("specifications", specifications);
                        map.put("model", model);//型号
//                        //判断物料名称+型号库里不能重复
//                        List<Map<String, Object>> bySql6 = handlerService.findBySql("select * from gq_item where description = '" + description + "' and model = " + "'" + model + "'");
//                        if(CollectionUtils.isNotEmpty(bySql6)){
//                            throw new ServiceException("第"+line+"行的物料名称为"+description+"型号为"+model+"的物料在系统中已存在，请重新输入");
//                        }
                        if (StringUtils.isNotBlank(supplier)) {
                            List<Map<String, Object>> bySql1 = handlerService.findBySql("select id from gq_account where suppliername = " + "'" + supplier + "'");
                            if (CollectionUtils.isEmpty(bySql1)) {
                                throw new ServiceException("第" + line + "行输入的供应商名为" + supplier + "在系统中不存在，请重新填写");
                            }
                            map.put("supplier", supplier);//供应商
                        }
                        map.put("unit", unit);
                        List<Map<String, Object>> bySql2 = handlerService.findBySql("select maxdesc from gq_itemtype where maxnum = '" + maxnum + "'");
                        if (CollectionUtils.isEmpty(bySql2)) {
                            throw new ServiceException("第" + line + "行输入的大类编码为" + maxnum + "在系统中不存在，请重新填写");
                        }
                        map.put("maxnum", maxnum);
                        map.put("maxdesc", MapUtils.getString(bySql2.get(0), "maxdesc"));
                        List<Map<String, Object>> bySql3 = handlerService.findBySql("select mediumdesc from gq_medium where maxnum = '" + maxnum + "' and mediumnum = '" + mediumnum + "'");
                        if (CollectionUtils.isEmpty(bySql3)) {
                            throw new ServiceException("第" + line + "行输入的中类编码为" + mediumnum + "没在大类编码为" + maxnum + "下面，请重新填写");
                        }
                        if (mediumnum.length() == 1) {
                            StringBuilder mediumnumNew = new StringBuilder().append(0).append(mediumnum);
                            map.put("mediumnum", mediumnumNew);
                        } else {
                            map.put("mediumnum", mediumnum);
                        }

                        map.put("mediumdesc", MapUtils.getString(bySql3.get(0), "mediumdesc"));
                        List<Map<String, Object>> bySql4 = handlerService.findBySql("select smalldesc from gq_small where maxnum = '" + maxnum + "' and mediumnum = '" + mediumnum + "' and smallnum = '" + smallnum + "'");
                        if (CollectionUtils.isEmpty(bySql4)) {
                            throw new ServiceException("第" + line + "行输入的小类编码为" + smallnum + "没在大类编码为" + maxnum + "和中类编码为" + mediumnum + "下面，请重新填写");
                        }
                        if (smallnum.length() == 1) {
                            StringBuilder smallnumNew = new StringBuilder().append(0).append(smallnum);
                            map.put("smallnum", smallnumNew);
                        } else {
                            map.put("smallnum", smallnum);
                        }

                        //成本类别
//                        List<Map<String, Object>> bySql1 = handlerService.findBySql("select id from gq_procostcategory where itemdesc = '" + pccdesc + "'");
//                        if(CollectionUtils.isNotEmpty(bySql1)){
//                            Map<String, Object> map1 = bySql1.get(0);
//                            String pccid = MapUtils.getString(map1, "id");
                        map.put("pccdesc", "材料费");//成本类别
                        map.put("pccid", 8);
//                        }

//                        if(StringUtils.isBlank(materialtion)){
                        map.put("materialtion", "CD0001");
//                        }else{
//                            map.put("materialtion",materialtion);
//                        }
//                        if(StringUtils.isBlank(matetegory)){
                        map.put("matetegory", "CN001");
//                        }else{
//                            map.put("matetegory",matetegory);
//                        }

                        /**
                         * 校验物料类型大类为1----封装形式是必填
                         * 为2-----标准号必填
                         * 为3-----材料牌号必填
                         */
                        if ("1".equals(maxnum)) {
                            if (StringUtils.isBlank(packform)) {
                                throw new ServiceException("第" + line + "行的物料大类编码为1，封装形式为必填！");
                            }
                            if (StringUtils.isBlank(qualitygrade)) {
                                throw new ServiceException("第" + line + "行的物料大类编码为1，质量/机械性能/精度为必填！");
                            }
                        }
                        if ("2".equals(maxnum)) {
                            if (StringUtils.isBlank(stexecunical)) {
                                throw new ServiceException("第" + line + "行的物料大类编码为2，执行/技术/标准号为必填！");
                            }
                        }
                        if ("3".equals(maxnum)) {
                            if (StringUtils.isBlank(matergrade)) {
                                throw new ServiceException("第" + line + "行的物料大类编码为3，材料牌号为必填！");
                            }
                        }
                        map.put("smalldesc", MapUtils.getString(bySql4.get(0), "smalldesc"));
                        map.put("spemodel", spemodel);//品牌
                        map.put("packform", packform);//封装形式
                        map.put("insstandard", insstandard);//检验标准
                        map.put("manufacturer", manufacturer);//生产厂家
                        map.put("surfacement", surfacement);//表面处理
                        map.put("stexecunical", stexecunical);//标准号/执行标准号/技术标准号
                        map.put("matergrade", matergrade);//材料牌号
                        map.put("versionNo", versionNo);//版本号
                        map.put("maintechtors", maintechtors);//主要技术指标
                        map.put("taxrate", "0.13");//税率
                        map.put("securitystock", securitystock);//安全库余量
                        map.put("loweststock", loweststock);//最低库余量
                        map.put("orgcode", "01");//所属组织

                        //拼接大中小类编码+描述
                        String maxdesc = MapUtils.getString(bySql2.get(0), "maxdesc");
                        String mediumdesc = MapUtils.getString(bySql3.get(0), "mediumdesc");
                        String smalldesc = MapUtils.getString(bySql4.get(0), "smalldesc");
                        //大中小类名称拼接
                        StringBuilder materialtypeStr2 = new StringBuilder();
                        materialtypeStr2.append(maxdesc).append("/").append(mediumdesc).append("/").append(smalldesc);
                        map.put("materialtypestring",(maxnum+mediumnum+smallnum));
                        map.put("materialtype",materialtypeStr2.toString());


                        handlerService.insert("gq_item", map);
                    }
                }
            }
            return Result.succeed("导入成功");
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return Result.failed(e.getMessage());
        }
    }

    /**
     * 物料主数据绵阳格式数据导出
     *
     * @param response
     * @throws IOException
     */
    @PostMapping("/mianyangExportExcel")
    public void mianyangExportExcel(HttpServletResponse response, @RequestParam String ids) throws IOException {

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("Sheet1");
        HSSFRow row = sheet.createRow(0);

        HSSFFont redFont = wb.createFont();
        redFont.setColor(Font.COLOR_RED);
        HSSFCellStyle style = wb.createCellStyle();
        style.setFont(redFont);
        style.setWrapText(true);
        style.setAlignment(HorizontalAlignment.LEFT);// 左右居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中

        HSSFCell cell = row.createCell(0);
        sheet.setColumnWidth(0, sheet.getColumnWidth(13) * 17 / 2);
        cell.setCellStyle(style);
        cell.setCellValue("*导入须知:" + "\r\n" + "1.表格中不能增、删、改列及固有内容" + "\r\n" +
                "2.所有内容必须为文本格式;表格中有多个档案名称字段是为了实现多语,如果没有多语只录第一个名称字段即可" + "\r\n" +
                "3.枚举项输入错误时，则按默认值处理;勾选框的导入需输入N、Y" + "\r\n" +
                "4.导入带有子表的档案时,表格中主表与子表之间必须有一空行,且主、子表对应数据需加上相同的行号");

        int itemline = 1;
        int unitline = 3;


        List<Map<String, Object>> bySql = new ArrayList<>();
        if (StringUtils.isNotBlank(ids)) {
            bySql = handlerService.findBySql("select * from gq_item where id in (" + ids + ")");
        } else {
            bySql = handlerService.findBySql("select * from gq_item order by createtime desc");
        }
        if (CollectionUtils.isNotEmpty(bySql)) {
            for (Map<String, Object> map : bySql) {
                itemLineTitle(sheet, style, itemline);//第一个标头

                int lineNew = itemline + 1;
                HSSFRow row1 = sheet.createRow(lineNew);
                row1.createCell(1).setCellValue(MapUtils.getString(map, "orgcode") != null ? MapUtils.getString(map, "orgcode") : "");//所属组织待定
                row1.createCell(2).setCellValue(MapUtils.getString(map, "itemnum"));//物料编码
                row1.createCell(3).setCellValue(MapUtils.getString(map, "description"));//物料名称
//                row1.createCell(4).setCellValue(MapUtils.getString(map,"description"));//物料名称英文名称
                row1.createCell(5).setCellValue(MapUtils.getString(map, "versionNo"));//版本号
                if (StringUtils.isNotBlank(MapUtils.getString(map, "itemnum")) && MapUtils.getString(map, "itemnum").length() > 5) {
                    row1.createCell(6).setCellValue(MapUtils.getString(map, "materialtion"));//物料分类
                }
                row1.createCell(7).setCellValue(MapUtils.getString(map, "specifications"));//规格
                row1.createCell(8).setCellValue(MapUtils.getString(map, "model"));//型号
//                row1.createCell(9).setCellValue(MapUtils.getString(map,"itemnum").substring(0,5));//图号
                row1.createCell(10).setCellValue(MapUtils.getString(map, "unit"));//计量单位·
                row1.createCell(11).setCellValue(MapUtils.getString(map, "unit"));//服务类
                row1.createCell(12).setCellValue(MapUtils.getString(map, "matetegory"));//物料税类
//                row1.createCell(13).setCellValue(MapUtils.getString(map,"matetegory"));//入库容差
//                row1.createCell(14).setCellValue(MapUtils.getString(map,"matetegory"));//出库容差
//                row1.createCell(15).setCellValue(MapUtils.getString(map,"matetegory"));//采购参考价格
//                row1.createCell(16).setCellValue(MapUtils.getString(map,"matetegory"));//采购周期
//                row1.createCell(17).setCellValue(MapUtils.getString(map,"matetegory"));//生产周期
//                row1.createCell(18).setCellValue(MapUtils.getString(map,"matetegory"));//最高价
//                row1.createCell(19).setCellValue(MapUtils.getString(map,"matetegory"));//最新价
                row1.createCell(20).setCellValue(MapUtils.getString(map, "manufacturer"));//生产厂家
                row1.createCell(21).setCellValue(MapUtils.getString(map, "stexecunical"));//标准号/执行标准号/技术标准号
//                row1.createCell(22).setCellValue(MapUtils.getString(map,"matetegory"));//技术协议号
                row1.createCell(23).setCellValue(MapUtils.getString(map, "maintechtors"));//主要技术指标
                row1.createCell(24).setCellValue(MapUtils.getString(map, "packform"));//封装形式
                row1.createCell(25).setCellValue(MapUtils.getString(map, "qualitygrade"));//质量等级/机械性能等级/精度等级
                row1.createCell(26).setCellValue(MapUtils.getString(map, "surfacement"));//表面处理
                row1.createCell(27).setCellValue(MapUtils.getString(map, "status"));//* 启用状态
                row1.createCell(28).setCellValue(MapUtils.getString(map, "matergrade"));//材料牌号
//                row1.createCell(29).setCellValue(MapUtils.getString(map,"matetegory"));//所属型号
//                row1.createCell(30).setCellValue(MapUtils.getString(map,"matetegory"));//品种标准号
//                row1.createCell(31).setCellValue(MapUtils.getString(map,"matetegory"));//原材料供应状态
//                row1.createCell(32).setCellValue(MapUtils.getString(map,"matetegory"));//建议供应商及联系方式
//                row1.createCell(33).setCellValue(MapUtils.getString(map,"matetegory"));//合同分类
//                row1.createCell(34).setCellValue(MapUtils.getString(map,"matetegory"));//合同号
//                row1.createCell(35).setCellValue(MapUtils.getString(map,"matetegory"));//备注
//                row1.createCell(36).setCellValue(MapUtils.getString(map,"matetegory"));//辅助属性结构
//                row1.createCell(37).setCellValue(MapUtils.getString(map,"matetegory"));//库存状态
//                row1.createCell(38).setCellValue(MapUtils.getString(map,"matetegory"));//项目
//                row1.createCell(39).setCellValue(MapUtils.getString(map,"matetegory"));//供应商
//                row1.createCell(40).setCellValue(MapUtils.getString(map,"matetegory"));//生产厂商
//                row1.createCell(41).setCellValue(MapUtils.getString(map,"matetegory"));//客户
//                row1.createCell(42).setCellValue(MapUtils.getString(map,"matetegory"));//特征码
//                row1.createCell(43).setCellValue(MapUtils.getString(map,"matetegory"));//生产批号
//                row1.createCell(44).setCellValue(MapUtils.getString(map,"matetegory"));//组件代号
//                row1.createCell(45).setCellValue(MapUtils.getString(map,"matetegory"));//所属统一号
//                row1.createCell(46).setCellValue(MapUtils.getString(map,"matetegory"));//其他系统批次号
//                row1.createCell(47).setCellValue(MapUtils.getString(map,"matetegory"));//其他系统要求是否同批
//                row1.createCell(48).setCellValue(MapUtils.getString(map,"matetegory"));//创建人
//                row1.createCell(49).setCellValue(MapUtils.getString(map,"matetegory"));//创建时间
//                row1.createCell(50).setCellValue(MapUtils.getString(map,"matetegory"));//最后修改人
//                row1.createCell(51).setCellValue(MapUtils.getString(map,"matetegory"));//最后修改时间

                unitLineTitle(sheet, style, unitline);//第二个表头

                int unitlineNew = unitline + 1;
                HSSFRow row2 = sheet.createRow(unitlineNew);
                row2.createCell(1).setCellValue(MapUtils.getString(map, "unit"));//* 计量单位名称
//                row2.createCell(2).setCellValue(MapUtils.getString(map,"unit"));//* 主单位/辅单位
//                row2.createCell(3).setCellValue(MapUtils.getString(map,"unit"));//固定换算
//                row2.createCell(4).setCellValue(MapUtils.getString(map,"unit"));//结存
//                row2.createCell(5).setCellValue(MapUtils.getString(map,"unit"));//采购默认单位
//                row2.createCell(6).setCellValue(MapUtils.getString(map,"unit"));//生产默认单位
//                row2.createCell(7).setCellValue(MapUtils.getString(map,"unit"));//库存默认单位
//                row2.createCell(8).setCellValue(MapUtils.getString(map,"unit"));//销售默认单位
//                row2.createCell(9).setCellValue(MapUtils.getString(map,"unit"));//零售默认单位
//                row2.createCell(10).setCellValue(MapUtils.getString(map,"unit"));//件数管理
                itemline = itemline + 4;
                unitline = unitline + 4;
            }
        }

        //输出Excel文件
        OutputStream output = response.getOutputStream();
        response.reset();
        String exportFileName = "测试中文名称.xlsx";
        String fileNameURL = URLEncoder.encode(exportFileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileNameURL + ";" + "filename*=utf-8''" + fileNameURL);
        response.setContentType("application/x-xls");
        wb.write(output);
        output.close();
    }

    private void unitLineTitle(HSSFSheet sheet, HSSFCellStyle style, int unitline) {
        HSSFRow row1 = sheet.createRow(unitline);
        row1.createCell(0).setCellValue("\"materialconvert,pk_measdoc,measrate,fixedflag,isstorebalance,ispumeasdoc,isprodmeasdoc,isstockmeasdoc,issalemeasdoc,isretailmeasdoc,ispiecemangage\"");
        HSSFCell cell = row1.createCell(1);
        cell.setCellStyle(style);
        cell.setCellValue("* 计量单位名称");
        HSSFCell cell1 = row1.createCell(2);
        cell1.setCellStyle(style);
        cell1.setCellValue("* 主单位/辅单位");
        row1.createCell(3).setCellValue("固定换算");
        row1.createCell(4).setCellValue("结存");
        row1.createCell(5).setCellValue("采购默认单位");
        row1.createCell(6).setCellValue("生产默认单位");
        row1.createCell(7).setCellValue("库存默认单位");
        row1.createCell(8).setCellValue("销售默认单位");
        row1.createCell(9).setCellValue("零售默认单位");
        row1.createCell(10).setCellValue("件数管理");
    }

    private void itemLineTitle(HSSFSheet sheet, HSSFCellStyle style, int itemline) {
        HSSFRow row1 = sheet.createRow(itemline);
        row1.createCell(0).setCellValue("\"pk_order_b_$head,pk_org,code,name,ename,version,pk_marbasclass,materialspec,materialtype,graphid,pk_measdoc,fee,pk_mattaxes,intolerance,outtolerance,def1,def2,def34,def3,def4,def5,def6,def7,def8,def9,def10,def11,enablestate,def23,def24,def25,def26,def32,def39,def40,memo,marasstframe,marasst1,marasst2,marasst3,marasst4,marasst5,marasst100,marasst8,marasst9,marasst10,marasst14,marasst15,creator,creationtime,modifier,modifiedtime\"");
        HSSFCell cell = row1.createCell(1);
        cell.setCellStyle(style);
        cell.setCellValue("* 所属组织");
        HSSFCell cell1 = row1.createCell(2);
        cell1.setCellStyle(style);
        cell1.setCellValue("* 物料编码");
        HSSFCell cell2 = row1.createCell(3);
        cell2.setCellStyle(style);
        cell2.setCellValue("* 物料名称(ZH)");
        row1.createCell(4).setCellValue("英文名称");
        HSSFCell cell3 = row1.createCell(5);
        cell3.setCellStyle(style);
        cell3.setCellValue("* 版本号");
        HSSFCell cell4 = row1.createCell(6);
        cell4.setCellStyle(style);
        cell4.setCellValue("* 物料分类");
        row1.createCell(7).setCellValue("规格");
        row1.createCell(8).setCellValue("型号");
        row1.createCell(9).setCellValue("图号");
        HSSFCell cell5 = row1.createCell(10);
        cell5.setCellStyle(style);
        cell5.setCellValue("* 计量单位");
        row1.createCell(11).setCellValue("服务类");
        HSSFCell cell6 = row1.createCell(12);
        cell6.setCellStyle(style);
        cell6.setCellValue("* 物料税类");
        HSSFCell cell7 = row1.createCell(13);
        cell7.setCellStyle(style);
        cell7.setCellValue("* 入库容差（%）");
        HSSFCell cell8 = row1.createCell(14);
        cell8.setCellStyle(style);
        cell8.setCellValue("* 出库容差（%）");
        row1.createCell(15).setCellValue("采购参考价格");
        row1.createCell(16).setCellValue("采购周期");
        row1.createCell(17).setCellValue("生产周期");
        row1.createCell(18).setCellValue("最高价");
        row1.createCell(19).setCellValue("最新价");
        row1.createCell(20).setCellValue("生产厂家");
        row1.createCell(21).setCellValue("标准号/执行标准号/技术标准号");
        row1.createCell(22).setCellValue("技术协议号");
        row1.createCell(23).setCellValue("主要技术指标");
        row1.createCell(24).setCellValue("封装形式");
        row1.createCell(25).setCellValue("质量等级/机械性能等级/精度等级");
        row1.createCell(26).setCellValue("表面处理");
        HSSFCell cell9 = row1.createCell(27);
        cell9.setCellStyle(style);
        cell9.setCellValue("* 启用状态");
        row1.createCell(28).setCellValue("材料牌号");
        row1.createCell(29).setCellValue("所属型号");
        row1.createCell(30).setCellValue("品种标准号");
        row1.createCell(31).setCellValue("原材料供应状态");
        row1.createCell(32).setCellValue("建议供应商及联系方式");
        row1.createCell(33).setCellValue("合同分类");
        row1.createCell(34).setCellValue("合同号");
        row1.createCell(35).setCellValue("备注");
        row1.createCell(36).setCellValue("辅助属性结构");
        row1.createCell(37).setCellValue("库存状态");
        row1.createCell(38).setCellValue("项目");
        row1.createCell(39).setCellValue("供应商");
        row1.createCell(40).setCellValue("生产厂商");
        row1.createCell(41).setCellValue("客户");
        row1.createCell(42).setCellValue("特征码");
        row1.createCell(43).setCellValue("生产批号");
        row1.createCell(44).setCellValue("组件代号");
        row1.createCell(45).setCellValue("所属统一号");
        row1.createCell(46).setCellValue("其他系统批次号");
        row1.createCell(47).setCellValue("其他系统要求是否同批");
        row1.createCell(48).setCellValue("创建人");
        row1.createCell(49).setCellValue("创建时间");
        row1.createCell(50).setCellValue("最后修改人");
        row1.createCell(51).setCellValue("最后修改时间");
    }

    /**
     * 物料主数据erp该系统格式数据导出1
     *
     * @param response
     * @throws IOException
     */
    @PostMapping("/erpExportExcel")
    public void erpExportExcel(HttpServletResponse response, @RequestParam String ids) throws IOException {

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("Sheet1");
        HSSFRow row = sheet.createRow(0);

        row.createCell(0).setCellValue("物料编码");
        row.createCell(1).setCellValue("物料名称");
        row.createCell(2).setCellValue("品牌");
        row.createCell(3).setCellValue("型号");
        row.createCell(4).setCellValue("生产厂家");
        row.createCell(5).setCellValue("物料大类编码");
        row.createCell(6).setCellValue("物料中类编码");
        row.createCell(7).setCellValue("物料小类编码");
        row.createCell(8).setCellValue("状态");

        int rownum = 1;
        List<Map<String, Object>> bySql = new ArrayList<>();

        if (StringUtils.isNotBlank(ids)) {
            bySql = handlerService.findBySql("select * from gq_item where id in (" + ids + ")");
        } else {
            bySql = handlerService.findBySql("select * from gq_item order by createtime desc");
        }
        if (CollectionUtils.isNotEmpty(bySql)) {
            for (Map<String, Object> map : bySql) {
                HSSFRow row1 = sheet.createRow(rownum);
                row1.createCell(0).setCellValue(MapUtils.getString(map, "itemnum"));
                row1.createCell(1).setCellValue(MapUtils.getString(map, "description"));
                row1.createCell(2).setCellValue(MapUtils.getString(map, "spemodel"));
                row1.createCell(3).setCellValue(MapUtils.getString(map, "model"));
                row1.createCell(4).setCellValue(MapUtils.getString(map, "manufacturer"));
                row1.createCell(5).setCellValue(MapUtils.getString(map, "maxnum"));
                row1.createCell(6).setCellValue(MapUtils.getString(map, "mediumnum"));
                row1.createCell(7).setCellValue(MapUtils.getString(map, "smallnum"));
                row1.createCell(8).setCellValue(MapUtils.getString(map, "status"));
                rownum++;
            }
        }

        //输出Excel文件
        OutputStream output = response.getOutputStream();
        response.reset();
        String exportFileName = "物料主数据导出.xls";
        String fileNameURL = URLEncoder.encode(exportFileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileNameURL + ";" + "filename*=utf-8''" + fileNameURL);
        response.setContentType("application/x-xls");
        wb.write(output);
        output.close();

    }


    /**
     * 物料类型导入模板导出
     *
     * @param response
     * @throws IOException
     */
    @PostMapping("/itemTypeExportExcel")
    public void itemTypeExportExcel(HttpServletResponse response) throws IOException {

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("物料类型");
        HSSFRow row = sheet.createRow(0);

        HSSFFont redFont = wb.createFont();
        redFont.setColor(Font.COLOR_RED);
        HSSFCellStyle style = wb.createCellStyle();
        style.setFont(redFont);
        style.setWrapText(true);
        style.setAlignment(HorizontalAlignment.LEFT);// 左右居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中

        HSSFCellStyle style1 = wb.createCellStyle();
        HSSFDataFormat format = wb.createDataFormat();
        style1.setDataFormat(format.getFormat("@"));
        sheet.setDefaultColumnStyle(2, style1);
        sheet.setDefaultColumnStyle(4, style1);

        //给第一行里面添数据
        HSSFCell cell1 = row.createCell(0);
        cell1.setCellStyle(style);
        cell1.setCellValue("*物料大类编码");
        row.createCell(1).setCellValue("物料大类名称");
        HSSFCell cell2 = row.createCell(2);
        cell2.setCellStyle(style);
        cell2.setCellValue("*物料中类编码");
        row.createCell(3).setCellValue("物料中类名称");
        HSSFCell cell3 = row.createCell(4);
        cell3.setCellStyle(style);
        cell3.setCellValue("*物料小类编码");
        row.createCell(5).setCellValue("物料小类名称");


        //输出Excel文件
        OutputStream output = response.getOutputStream();
        response.reset();
        String exportFileName = "物料类型导入模板.xls";
        String fileNameURL = URLEncoder.encode(exportFileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileNameURL + ";" + "filename*=utf-8''" + fileNameURL);
        response.setContentType("application/x-xls");
        wb.write(output);
        output.close();
    }


    @PostMapping("/itemTypeImportExcel")
    @Transactional(rollbackFor = Exception.class)
    public Result itemTypeImportExcel(@RequestParam("file") MultipartFile myfile) throws Exception {
        try {
            Workbook workbook = null;
            String fileName = myfile.getOriginalFilename();
            if (fileName.endsWith(XLS)) {//获取文件后缀
                workbook = new HSSFWorkbook(myfile.getInputStream());
            } else if (fileName.endsWith(XLSX)) {
                workbook = new XSSFWorkbook(myfile.getInputStream());
            } else {
                throw new Exception("文件不是excel文件");
            }

            //获取excel的Sheet页
            Sheet sheet1 = workbook.getSheet("物料类型");
            //判断excel一共多少行
            int rows = sheet1.getLastRowNum();
            if (rows == 0) {
                //excel里面没数据
                throw new Exception("请填写数据");
            }

            List<Map<String, Object>> maxMapsList = new ArrayList<>();
            List<Map<String, Object>> mediuMapsList = new ArrayList<>();
            List<Map<String, Object>> smallMapsList = new ArrayList<>();

            for (int i = 3; i <= rows; i++) {
                Map<String, Object> maxMap = new HashMap<>();
                Map<String, Object> mediuMap = new HashMap<>();
                Map<String, Object> smallMap = new HashMap<>();

                Row row = sheet1.getRow(i);
                if (row != null) {
                    String maxnum = getCellValue(row, 0).toString().trim();
                    String maxdesc = getCellValue(row, 1).toString().trim();
                    String mediumnum = getCellValue(row, 2).toString().trim();
                    String mediumdesc = getCellValue(row, 3).toString().trim();
                    String smallnum = getCellValue(row, 4).toString().trim();
                    String smalldesc = getCellValue(row, 5).toString().trim();
                    //判断模板是否正确
                    if (i == 3) {
                        if (!"*物料大类编码".equals(maxnum) || !"*物料大类名称".equals(maxdesc) || !"*物料中类编码".equals(mediumnum)
                                || !"*物料中类名称".equals(mediumdesc) || !"*物料小类编码".equals(smallnum) || !"*物料小类名称".equals(smalldesc)) {
                            throw new ServiceException("导入的模板错误，请下载模板进行导入");
                        }
                    } else {
                        //检验必填项
                        if (StringUtils.isBlank(maxnum) || StringUtils.isBlank(mediumnum) || StringUtils.isBlank(smallnum)) {
                            throw new ServiceException("有必填字段未填写。填写后重新导入");
                        }

                        //判断大物料类型
                        List<Map<String, Object>> bySql1 = handlerService.findBySql("select count(0) as count from gq_itemtype where maxnum = '" + maxnum + "'");
                        if (MapUtils.getInteger(bySql1.get(0), "count") > 0) {
                            //存在该大类.查大下的中
                            List<Map<String, Object>> bySql = handlerService.findBySql("select count(0) as count from gq_medium where maxnum = '" + maxnum + "' and mediumnum = '" + mediumnum + "'");
                            if (MapUtils.getInteger(bySql.get(0), "count") > 0) {
                                //存在该大类，中类，查其下的小类
                                List<Map<String, Object>> bySql2 = handlerService.findBySql("select count(0) as count from gq_small where maxnum  = '" + maxnum + "' and mediumnum = '" + mediumnum + "' and smallnum = '" + smallnum + "'");
                                if (MapUtils.getInteger(bySql2.get(0), "count") > 0) {
                                    int line = i + 1;
                                    throw new ServiceException("第" + line + "行填写的物料类型已存在，请修改后再进行导入");
                                } else {
                                    smallMap.put("maxnum", maxnum);
                                    smallMap.put("smallnum", smallnum);
                                    smallMap.put("smalldesc", smalldesc);
                                    smallMap.put("mediumnum", mediumnum);
                                    smallMap.put("mediumdesc", mediumdesc);
                                    smallMap.put("uuidprice",IdUtil.simpleUUID());//uuid
                                    smallMap.put("materialtypestring",(maxnum+mediumnum+smallnum));//物料类型串(包含所有父+自己）
                                    handlerService.insert("gq_small", smallMap);
                                }
                            } else {
                                //中
                                mediuMap.put("maxnum", maxnum);
                                mediuMap.put("mediumdesc", mediumdesc);
                                mediuMap.put("mediumnum", mediumnum);
                                mediuMap.put("uuidprice",IdUtil.simpleUUID());//uuid
                                mediuMap.put("materialtypestring",(maxnum+mediumnum));//物料类型串(包含所有父+自己）
                                handlerService.insert("gq_medium", mediuMap);
                                //小
                                smallMap.put("maxnum", maxnum);
                                smallMap.put("smallnum", smallnum);
                                smallMap.put("smalldesc", smalldesc);
                                smallMap.put("mediumnum", mediumnum);
                                smallMap.put("mediumdesc", mediumdesc);
                                smallMap.put("uuidprice",IdUtil.simpleUUID());//uuid
                                smallMap.put("materialtypestring",(maxnum+mediumnum+smallnum));//物料类型串(包含所有父+自己）
                                handlerService.insert("gq_small", smallMap);
                            }
                        } else {
                            //大
                            maxMap.put("maxnum", maxnum);
                            maxMap.put("maxdesc", maxdesc);
                            maxMap.put("uuidprice",IdUtil.simpleUUID());//uuid
                            maxMap.put("materialtypestring",maxnum);//物料类型串(包含所有父+自己）
                            handlerService.insert("gq_itemtype", maxMap);
                            //中
                            mediuMap.put("maxnum", maxnum);
                            mediuMap.put("mediumdesc", mediumdesc);
                            mediuMap.put("mediumnum", mediumnum);
                            mediuMap.put("uuidprice",IdUtil.simpleUUID());//uuid
                            mediuMap.put("materialtypestring",(maxnum+mediumnum));//物料类型串(包含所有父+自己）
                            handlerService.insert("gq_medium", mediuMap);
                            //小
                            smallMap.put("maxnum", maxnum);
                            smallMap.put("smallnum", smallnum);
                            smallMap.put("smalldesc", smalldesc);
                            smallMap.put("mediumnum", mediumnum);
                            smallMap.put("mediumdesc", mediumdesc);
                            smallMap.put("uuidprice",IdUtil.simpleUUID());//uuid
                            smallMap.put("materialtypestring",(maxnum+mediumnum+smallnum));//物料类型串(包含所有父+自己）
                            handlerService.insert("gq_small", smallMap);
                        }
                    }
                }
            }
            return Result.succeed("导入成功");
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return Result.failed(e.getMessage());
        }
    }

    /**
     * 物料编码申请导入模板导出
     *
     * @param response
     * @throws IOException
     */
    @PostMapping("/itemCodeApplyExportExcel")
    public void itemCodeApplyExportExcel(HttpServletResponse response) throws IOException {

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("Sheet1");
        HSSFRow row = sheet.createRow(0);

        HSSFFont redFont = wb.createFont();
        redFont.setColor(Font.COLOR_RED);
        HSSFCellStyle style = wb.createCellStyle();
        style.setFont(redFont);
//        style.setWrapText(true);
//        style.setAlignment(HorizontalAlignment.LEFT);// 左右居中
//        style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中

        HSSFCellStyle style1 = wb.createCellStyle();
        HSSFDataFormat format = wb.createDataFormat();
        style1.setDataFormat(format.getFormat("@"));
        sheet.setDefaultColumnStyle(1, style1);
        sheet.setDefaultColumnStyle(2, style1);

        //给第一行里面添数据
        HSSFCell cell1 = row.createCell(0);
        cell1.setCellStyle(style);
        cell1.setCellValue("大类编码");
        HSSFCell cell2 = row.createCell(1);
        cell2.setCellStyle(style);
        cell2.setCellValue("中类编码");
        HSSFCell cell3 = row.createCell(2);
        cell3.setCellStyle(style);
        cell3.setCellValue("小类编码");
        HSSFCell cell4 = row.createCell(3);
        cell4.setCellStyle(style);
        cell4.setCellValue("物料名称");
        HSSFCell cell5 = row.createCell(4);
        cell5.setCellStyle(style);
        cell5.setCellValue("计量单位");
        row.createCell(5).setCellValue("规格");
        HSSFCell cell6 = row.createCell(6);
        cell6.setCellStyle(style);
        cell6.setCellValue("型号");
        row.createCell(7).setCellValue("寿命（月）");
        HSSFCell cell7 = row.createCell(8);
        cell7.setCellStyle(style);
        cell7.setCellValue("品牌");
        HSSFCell cell8 = row.createCell(9);
        cell8.setCellStyle(style);
        cell8.setCellValue("生产厂家");
        row.createCell(10).setCellValue("建议供应商编码");
        row.createCell(11).setCellValue("建议供应商名称");
        row.createCell(12).setCellValue("封装形式");
        row.createCell(13).setCellValue("质量/机械性能/精度");
        row.createCell(14).setCellValue("表面处理");
        row.createCell(15).setCellValue("执行/技术/标准号");
        row.createCell(16).setCellValue("材料牌号");
//        row.createCell(17).setCellValue("物料税类");
        row.createCell(17).setCellValue("版本号");
//        row.createCell(19).setCellValue("物料分类");
        HSSFCell cell9 = row.createCell(18);
        cell9.setCellStyle(style);
        cell9.setCellValue("主要技术指标");


//        String dj="DJ"; //紧急程度
//        Map<String, String> djmap = new HashMap<>();
//        djmap.put("DJ","国家鲁班奖,优良,合格,不合格");
//        //指定将下拉框添加至1-10000行，0-0列。即第一列的第2到10001行
//        HSSFDataValidation dataValidation7 = createBox(dj, djmap, 1,10000 , 13, 13);
//        if (dataValidation7 != null) {
//            sheet.addValidationData(dataValidation7);
//        }

        //输出Excel文件
        OutputStream output = response.getOutputStream();
        response.reset();
        String exportFileName = "物料编码申请导入模板.xls";
        String fileNameURL = URLEncoder.encode(exportFileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileNameURL + ";" + "filename*=utf-8''" + fileNameURL);
        response.setContentType("application/x-xls");
        wb.write(output);
        output.close();
    }

    @PostMapping("/itemCodeApplyImportExcel")
    @Transactional(rollbackFor = Exception.class)
    public Result itemCodeApplyImportExcel(@RequestParam("file") MultipartFile myfile, @RequestParam("id") String id) throws Exception {
        try {
            Workbook workbook = null;
            String fileName = myfile.getOriginalFilename();
            if (fileName.endsWith(XLS)) {//获取文件后缀
                workbook = new HSSFWorkbook(myfile.getInputStream());
            } else if (fileName.endsWith(XLSX)) {
                workbook = new XSSFWorkbook(myfile.getInputStream());
            } else {
                throw new Exception("文件不是excel文件");
            }

            //获取excel的Sheet页
            Sheet sheet1 = workbook.getSheet("Sheet1");
            //判断excel一共多少行
            int rows = sheet1.getLastRowNum();
            if (rows == 0) {
                //excel里面没数据
                throw new Exception("请填写数据");
            }
            List<Map<String, Object>> bySql1 = handlerService.findBySql("select applicanum from gq_macoapplica where id = '" + id + "'");
            if (CollectionUtils.isEmpty(bySql1)) {
                throw new ServiceException("未接收到物料编码申请主数据");
            }
            String applicanum = MapUtils.getString(bySql1.get(0), "applicanum");

            for (int i = 0; i <= rows; i++) {
                Row row = sheet1.getRow(i);
                if (row != null) {
                    Map<String, Object> resultMap = new HashMap<>();
                    String categorycode = getCellValue(row, 0).toString().trim();//大类编码
                    String middlecode = getCellValue(row, 1).toString().trim();//中类编码
                    String subcatecode = getCellValue(row, 2).toString().trim();//小类编码
                    String description = getCellValue(row, 3).toString().trim();//物料名称
                    String unit = getCellValue(row, 4).toString().trim();//计量单位
                    String specifications = getCellValue(row, 5).toString().trim();//规格
                    String model = getCellValue(row, 6).toString().trim();//型号
                    String life = getCellValue(row, 7).toString().trim();//寿命（月）
                    String spemodel = getCellValue(row, 8).toString().trim();//品牌
                    String manufacturer = getCellValue(row, 9).toString().trim();//生产厂家
                    String supplierno = getCellValue(row, 10).toString().trim();//建议供应商编码
                    String advisupplier = getCellValue(row, 11).toString().trim();//建议供应商名称
                    String packform = getCellValue(row, 12).toString().trim();//封装形式
                    String qualitygrade = getCellValue(row, 13).toString().trim();//质量/机械性能/精度
                    String surfacement = getCellValue(row, 14).toString().trim();//表面处理
                    String stexecunical = getCellValue(row, 15).toString().trim();//执行/技术/标准号
                    String matergrade = getCellValue(row, 16).toString().trim();//材料牌号
//                    String matetegory = getCellValue(row, 17).toString().trim();//物料税类
                    String versionNo = getCellValue(row, 17).toString().trim();//版本号
//                    String materialtion = getCellValue(row, 19).toString().trim();//物料分类
                    String maintechtors = getCellValue(row, 18).toString().trim();//主要技术指标
                    if (i == 0) {
                        if (!"大类编码".equals(categorycode) || !"中类编码".equals(middlecode) || !"小类编码".equals(subcatecode)
                                || !"物料名称".equals(description) || !"计量单位".equals(unit) || !"规格".equals(specifications)
                                || !"型号".equals(model) || !"寿命（月）".equals(life) || !"品牌".equals(spemodel)
                                || !"生产厂家".equals(manufacturer) || !"建议供应商名称".equals(advisupplier) || !"封装形式".equals(packform)
                                || !"质量/机械性能/精度".equals(qualitygrade) || !"表面处理".equals(surfacement) || !"执行/技术/标准号".equals(stexecunical)
                                || !"材料牌号".equals(matergrade) || !"版本号".equals(versionNo)
                                || !"主要技术指标".equals(maintechtors) || !"建议供应商编码".equals(supplierno)) {
                            throw new ServiceException("导入的模板错误，请下载模板进行导入");
                        }
                    } else {
                        int line = i + 1;
                        //校验必填项
                        if (StringUtils.isBlank(categorycode) || StringUtils.isBlank(middlecode) || StringUtils.isBlank(subcatecode)
                                || StringUtils.isBlank(description) || StringUtils.isBlank(unit) || StringUtils.isBlank(model)
                                || StringUtils.isBlank(spemodel) || StringUtils.isBlank(manufacturer) || StringUtils.isBlank(maintechtors)) {
                            StringBuilder stbr = new StringBuilder("第" + line + "行,");
                            stbr.append(StringUtils.isBlank(categorycode) ? "大类编码," : "");
                            stbr.append(StringUtils.isBlank(middlecode) ? "中类编码," : "");
                            stbr.append(StringUtils.isBlank(subcatecode) ? "小类编码," : "");
                            stbr.append(StringUtils.isBlank(description) ? "物料名称," : "");
                            stbr.append(StringUtils.isBlank(unit) ? "计量单位," : "");
                            stbr.append(StringUtils.isBlank(model) ? "型号," : "");
                            stbr.append(StringUtils.isBlank(spemodel) ? "品牌," : "");
                            stbr.append(StringUtils.isBlank(manufacturer) ? "生产厂家," : "");
                            stbr.append(StringUtils.isBlank(maintechtors) ? "主要技术指标," : "");
                            String result = stbr.substring(0, stbr.lastIndexOf(",")) + "";
                            result = result + "不允许为空!";
                            throw new ServiceException(result);
                        }

                        //校验物料名称长度
                        if(description.length()>50){
                            throw new ServiceException("第" + line + "行的物料名称长度超过50个字符!");
                        }

                        //判断去重（名称+型号不能重复）
                        String sql = "select * from gq_materappline where applicanum = '" + applicanum + "' and description = '" + description + "'" + " and model = '" + model + "'";
                        String sqlAdd = " and specifications = '" + specifications + "'";
                        if (StringUtils.isNotBlank(specifications)) {
                            List<Map<String, Object>> bySql = handlerService.findBySql(sql + sqlAdd);
                            if (CollectionUtils.isNotEmpty(bySql)) {
                                throw new ServiceException("第" + line + "行物料名称为" + description + ",型号为" + model + ",规格为" + specifications + "的物料编码申请行信息模板数据已重复或系统已存在，请修改后再进行导入");
                            }
                        } else {
                            List<Map<String, Object>> bySql = handlerService.findBySql(sql);
                            if (CollectionUtils.isNotEmpty(bySql)) {
                                throw new ServiceException("第" + line + "行物料名称为" + description + ",型号为" + model + "的物料编码申请行信息模板数据已重复或系统已存在，请修改后再进行导入");
                            }
                        }
                        //判断去重（后加的 品牌+型号不能重复）
                        List<Map<String, Object>> bySql6 = handlerService.findBySql("select * from gq_materappline where applicanum = '" + applicanum + "' and spemodel = '" + spemodel + "'" + " and model = '" + model + "'");
                        if (CollectionUtils.isNotEmpty(bySql6)) {
                            throw new ServiceException("第" + line + "行品牌" + spemodel + ",型号为" + model + "的物料编码申请行信息模板数据已重复或系统已存在，请修改后再进行导入");
                        }
                        //判断月份不能小于0
                        if (StringUtils.isNotBlank(life)) {
                            Integer lifeInt = Integer.valueOf(life);
                            if (lifeInt < 0) {
                                throw new ServiceException("第" + line + "行的寿命为负数，请修改后重新导入");
                            }
                        }

                        /**
                         * 校验物料类型大类为1----封装形式是必填
                         * 为2-----标准号必填
                         * 为3-----材料牌号必填
                         */
                        if ("1".equals(categorycode)) {
                            if (StringUtils.isBlank(packform)) {
                                throw new ServiceException("第" + line + "行的物料大类编码为1，封装形式为必填！");
                            }
                            if (StringUtils.isBlank(qualitygrade)) {
                                throw new ServiceException("第" + line + "行的物料大类编码为1，质量/机械性能/精度为必填！");
                            }
                        }
                        if ("2".equals(middlecode)) {
                            if (StringUtils.isBlank(stexecunical)) {
                                throw new ServiceException("第" + line + "行的物料大类编码为2，执行/技术/标准号为必填！");
                            }
                        }
                        if ("3".equals(subcatecode)) {
                            if (StringUtils.isBlank(matergrade)) {
                                throw new ServiceException("第" + line + "行的物料大类编码为3，材料牌号为必填！");
                            }
                        }


                        List<Map<String, Object>> bySql2 = handlerService.findBySql("select maxdesc from gq_itemtype where maxnum = '" + categorycode + "'");
                        if (CollectionUtils.isEmpty(bySql2)) {
                            throw new ServiceException("第" + line + "行输入的大类编码为" + categorycode + "在系统中不存在，请重新填写");
                        }
                        resultMap.put("categorycode", categorycode);
                        resultMap.put("catecodedesc", MapUtils.getString(bySql2.get(0), "maxdesc"));
                        List<Map<String, Object>> bySql3 = handlerService.findBySql("select mediumdesc from gq_medium where maxnum = '" + categorycode + "' and mediumnum = '" + middlecode + "'");
                        if (CollectionUtils.isEmpty(bySql3)) {
                            throw new ServiceException("第" + line + "行物料大类为" + MapUtils.getString(bySql2.get(0), "maxdesc") + "不存在中类编码" + middlecode + "！");
                        }
                        resultMap.put("middlecode", middlecode);
                        resultMap.put("middcodedesc", MapUtils.getString(bySql3.get(0), "mediumdesc"));
                        List<Map<String, Object>> bySql4 = handlerService.findBySql("select smalldesc from gq_small where maxnum = '" + categorycode + "' and mediumnum = '" + middlecode + "' and smallnum = '" + subcatecode + "'");
                        if (CollectionUtils.isEmpty(bySql4)) {
                            throw new ServiceException("第" + line + "行物料大类为" + MapUtils.getString(bySql2.get(0), "maxdesc") + ",中类为" + MapUtils.getString(bySql3.get(0), "mediumdesc") + "不存在小类编码" + subcatecode + "！");
                        }
                        resultMap.put("subcatecode", subcatecode);
                        resultMap.put("subcatecodedesc", MapUtils.getString(bySql4.get(0), "smalldesc"));

                        resultMap.put("description", description);
                        resultMap.put("unit", unit);
                        resultMap.put("specifications", specifications);
                        resultMap.put("model", model);
                        resultMap.put("life", life);
                        resultMap.put("spemodel", spemodel);
                        resultMap.put("manufacturer", manufacturer);
                        resultMap.put("applicanum", applicanum);

                        if (StringUtils.isNotBlank(supplierno)) {
                            List<Map<String, Object>> bySql = handlerService.findBySql("select * from gq_account where supplierno = '" + supplierno + "'");
                            if (CollectionUtils.isEmpty(bySql)) {
                                throw new ServiceException("第" + line + "行输入的供应商不存在。");
                            }
                            resultMap.put("supplierno", supplierno);
                            resultMap.put("advisupplier", MapUtils.getString(bySql.get(0), "suppliername"));
                        }

                        resultMap.put("packform", packform);
                        resultMap.put("qualitygrade", qualitygrade);
                        resultMap.put("surfacement", surfacement);
                        resultMap.put("stexecunical", stexecunical);
                        resultMap.put("matergrade", matergrade);
                        resultMap.put("matetegory", "CN001");
                        resultMap.put("versionNo", versionNo);
                        resultMap.put("materialtion", "CD0001");
                        resultMap.put("maintechtors", maintechtors);

                        handlerService.insert("gq_materappline", resultMap);
                    }
                }
            }
            return Result.succeed("导入成功");
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return Result.failed(e.getMessage());
        }
    }


    /**
     * 获取Cell内容
     */
    public Object getCellValue(Row row, int column) {
        Object val = "";
        try {
            Cell cell = row.getCell(column);
            if (cell != null) {

                if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                    val = cell.getNumericCellValue();
                    DecimalFormat df = new DecimalFormat("#.#########");
                    String cellValueStr = String.valueOf(val);
                    val = df.format(Double.valueOf(cellValueStr));
                } else if (cell.getCellTypeEnum() == CellType.STRING) {
                    val = cell.getStringCellValue();
                } else if (cell.getCellTypeEnum() == CellType.FORMULA) {
                    val = cell.getCellFormula();
                } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
                    val = cell.getBooleanCellValue();
                } else if (cell.getCellTypeEnum() == CellType.ERROR) {
                    val = cell.getErrorCellValue();
                }
            }
        } catch (Exception e) {
            return val;
        }
        return val;
    }

    public HSSFDataValidation createBox(String col, Map<String, String> boxMap, int firstRow, int lastRow, int firstCol, int lastCol) {
        HSSFDataValidation dataValidation = null;
        //查询码值表
        String cols = "";
        if (null != boxMap.get(col)) {
            cols = boxMap.get(col);
        }
        //设置下拉框
        if (cols.length() > 0 && null != cols) {
            String str[] = cols.split(",");
            CellRangeAddressList cas = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            //创建下拉数据列
            DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(str);
            //将下拉数据放入下拉框
            dataValidation = new HSSFDataValidation(cas, dvConstraint);
        }
        return dataValidation;
    }

    /**
     * 物料主数据---物料类型树
     *
     * @return
     */
    @GetMapping("/itemTypeTree")
    public Result itemTypeTree(@RequestParam Map<String, Object> parms) throws Exception {
        //父级是否可选
        Boolean issubset = MapUtils.getBoolean(parms, "issubset");
        //查大类编码
        Map<String, Object> map1 = queryItemByUser();
        String isPermission = MapUtils.getString(map1, "isPermission");
        CommonModel commonModel = (CommonModel) map1.get("commonModel");
        String sql = "select t.* from gq_itemtype t where 1=1 ";
        StringBuilder str = new StringBuilder();
//        if ("true".equals(isPermission)) {
//            str.append(sql).append(commonModel.getDsf());
//        } else {
//            str.append(sql);
//        }
        List<Map<String, Object>> itemtypeMaxnum = handlerService.findBySql(sql.toString());
        for (Map<String, Object> map : itemtypeMaxnum) {
            map.put("id", MapUtils.getString(map, "uuidprice"));
            String maxnum = MapUtils.getString(map, "maxnum");
            //中
            List<Map<String, Object>> bySql = handlerService.findBySql("select * from gq_medium where maxnum = '" + maxnum + "'");
            map.put("children", bySql);
            if (issubset != null) {
                map.put("issubset", issubset);
            } else {
                map.put("issubset", true);
            }
            map.put("choosefield", maxnum);
            map.put("maxdescnew", MapUtils.getString(map, "maxdesc") != null ? MapUtils.getString(map, "maxdesc") : null);
            StringBuilder materialtypeStr = new StringBuilder();
            materialtypeStr.append(MapUtils.getString(map, "maxdesc"));
            map.put("materialtype", materialtypeStr.toString());
            for (Map<String, Object> mediumMap : bySql) {
                mediumMap.put("id", MapUtils.getString(mediumMap, "uuidprice"));
                String mediumnum = MapUtils.getString(mediumMap, "mediumnum");
                //小
                List<Map<String, Object>> bySql1 = handlerService.findBySql("select a.*,b.maxdesc as maxdescnew from gq_small a left join gq_itemtype b on a.maxnum=b.maxnum  where a.maxnum = '" + maxnum + "' and a.mediumnum = '" + mediumnum + "'");
                mediumMap.put("children", bySql1);
                if (issubset != null) {
                    mediumMap.put("issubset", issubset);
                } else {
                    mediumMap.put("issubset", true);
                }
                mediumMap.put("maxdesc", MapUtils.getString(mediumMap, "mediumdesc"));
                mediumMap.put("choosefield", mediumnum);
                mediumMap.put("maxdescnew", MapUtils.getString(map, "maxdesc") != null ? MapUtils.getString(map, "maxdesc") : null);
                StringBuilder materialtypeStr1 = new StringBuilder();
                materialtypeStr1.append(MapUtils.getString(map, "maxdesc")).append("/").append(MapUtils.getString(mediumMap, "mediumdesc"));
                mediumMap.put("materialtype", materialtypeStr1.toString());
                for (Map<String, Object> stringObjectMap : bySql1) {
                    stringObjectMap.put("id", MapUtils.getString(stringObjectMap, "uuidprice"));
                    stringObjectMap.put("maxdesc", MapUtils.getString(stringObjectMap, "smalldesc"));
                    stringObjectMap.put("issubset", false);
                    stringObjectMap.put("choosefield", MapUtils.getString(stringObjectMap, "smallnum"));
                    //大中小类名称拼接
                    StringBuilder materialtypeStr2 = new StringBuilder();
                    materialtypeStr2.append(MapUtils.getString(map, "maxdesc")).append("/").append(MapUtils.getString(mediumMap, "mediumdesc")).append("/").append(MapUtils.getString(stringObjectMap, "smalldesc"));
                    stringObjectMap.put("materialtype", materialtypeStr2.toString());
                }
            }
        }
        return Result.succeed(itemtypeMaxnum, "操作成功");
    }

    /**
     * 获取所有物料类型
     *
     * @param map
     * @return
     * @throws Exception
     */
    @PostMapping("/getAllItemType")
    public Map<String, Object> getAllItemType(@RequestBody Map<String, Object> map) throws Exception {
        System.out.println("===> enter getAllItemType");
        System.out.println("===> map: " + map);
        String num = MapUtils.getString(map, "maxnum");
        String desc = MapUtils.getString(map, "maxdesc");
        Integer page = MapUtils.getInteger(map, "page");
        Integer limit = MapUtils.getInteger(map, "limit");
        String maxSql = "select uuidprice as id,id as rowid,materialtypestring as maxnum,maxdesc,'大类' as types,'大类无中类编码' as mediumnum,'大类无中类名称' as mediumdesc,maxnum as max from gq_itemtype where 1=1 ";
        String mediumSql = "select me.uuidprice as id,it.id as rowid,me.materialtypestring as maxnum,mediumdesc as maxdesc ,'中类' as types,me.mediumnum as mediumnum,me.mediumdesc as mediumdesc,me.maxnum as max from gq_medium me left join gq_itemtype it on me.maxnum =it.maxnum  where 1=1 ";
        String smallSql = "select sm.uuidprice as id,it.id as rowid,sm.materialtypestring as maxnum,smalldesc as maxdesc ,'小类' as types,sm.mediumnum as mediumnum,sm.mediumdesc as mediumdesc,sm.maxnum as max from gq_small sm left join gq_itemtype it on sm.maxnum =it.maxnum  where 1=1 ";
        if (StringUtils.isNotBlank(num)) {
            maxSql += " and maxnum like '%" + num+"%' ";
            mediumSql += " and mediumnum like '%" + num + "%' ";
            smallSql += " and smallnum like '%" + num + "%' ";
        }
        if (StringUtils.isNotBlank(desc)) {
            maxSql += " and maxdesc like '%" + desc + "%' ";
            mediumSql += " and me.mediumdesc like '%" + desc + "%' ";
            smallSql += " and sm.smalldesc like '%" + desc + "%' ";
        }

        if (StringUtils.isNotBlank(desc)) {
            List<Map<String, Object>> maxList = handlerService.findBySql(maxSql);
            System.out.println("===> maxList sql: " + maxSql);
            System.out.println("===> maxList.size: " + maxList.size());
            if (maxList.size() > 0) {
                for (Map<String, Object> max : maxList) {
                    mediumSql += "or it.maxnum = '" + MapUtils.getString(max, "maxnum") + "' ";
                    smallSql += "or it.maxnum = '" + MapUtils.getString(max, "maxnum") + "' ";
                }
            }
            List<Map<String, Object>> mediumList = handlerService.findBySql(mediumSql);
            System.out.println("===> mediumList sql: " + mediumSql);
            System.out.println("===> mediumList.size: " + mediumList.size());
            if (mediumList.size() > 0 && maxList.size() <= 0) {
                for (Map<String, Object> medium : mediumList) {
                    smallSql += "or (sm.mediumnum = '" + MapUtils.getString(medium, "mediumnum") + "' and sm.maxnum = '" + MapUtils.getString(medium, "max") + "') ";
                }
            }
        }

        String sql = maxSql + "UNION ALL " + mediumSql + "UNION ALL " + smallSql;
        List<Map<String, Object>> countList = handlerService.findBySql(sql);
        sql += "order by maxnum asc ";
        sql += " limit " + (page - 1) * limit + "," + limit + "";
        System.out.println("sql:" + sql);
        List<Map<String, Object>> result = handlerService.findBySql(sql);
        Map<String, Object> rsp = new HashMap<>();
        rsp.put("data", result);
        rsp.put("count", countList.size());
        rsp.put("code", 0);
        return rsp;
    }

    /**
     * 物料主数据历史数据展示最大最小平均值
     *
     * @param params
     * @return
     */
    @GetMapping("/getItemHispricemater")
    public Result getItemHispricemater(@RequestParam Map<String, Object> params) {
        Map<String, Object> map = new HashMap<>();

        String itemnum = MapUtils.getString(params, "itemnum");
        if (StringUtils.isBlank(itemnum)) {
            return Result.failed("未接收到物料编码");
        }
        String unitcostMax = "";
        String unitcostMain = "";
        String unitcostDivide = "";
        List<Map<String, Object>> bySql = handlerService.findBySql("select t.unitcost from gq_hispricemater t where t.delflag = '0' and t.itemnum = '" + itemnum + "' order by t.unitcost desc");
        if (CollectionUtils.isNotEmpty(bySql)) {
            unitcostMax = MapUtils.getString(bySql.get(0), "unitcost");//最高值
            unitcostMain = MapUtils.getString(bySql.get((bySql.size() - 1)), "unitcost");//最低值
            BigDecimal bigDecimal = new BigDecimal("0");
            for (Map<String, Object> stringObjectMap : bySql) {
                String unitcost = MapUtils.getString(stringObjectMap, "unitcost");
                bigDecimal = new BigDecimal(unitcost).add(bigDecimal);
            }
            unitcostDivide = bigDecimal.divide(new BigDecimal(bySql.size()), 2, BigDecimal.ROUND_HALF_UP).toString();//平均值
        }
        map.put("unitcostMax", unitcostMax);
        map.put("unitcostMain", unitcostMain);
        map.put("unitcostDivide", unitcostDivide);
        return Result.succeed(map, "操作成功");
    }

    public Map<String, Object> queryItemByUser() throws Exception {
        Map<String, Object> map = new HashMap<>();
        BaseHandler handler = null;
        BizApp appinfo = cfgRedisCache.getCacheApp("itemtypeApp");
        CommonModel commonModel = new CommonModel();
        BizObject object = cfgRedisCache.getCacheObjByApp("itemtypeApp");
        String handleclass = appinfo.getHandleclass();
        if (ToolUtil.isNotEmpty(handleclass)) {
            try {
                handler = HandlerFactory.getBeanHandler(handleclass);
            } catch (Exception e) {

            }
        }
        LoginAppUser loginAppUser = SysUserUtil.getLoginAppUser();
        boolean isPermission = true;
        if (AppConstant.USER_ADMIN.equals(loginAppUser.getUsername())) {
            isPermission = false;
        }
        if (isPermission) {
            if (AppConstant.BASE_NO.equals(appinfo.getDatalevel()) || ToolUtil.isEmpty(appinfo.getDatalevel())) {
                AppCommonUtil.setDataAuthorization(commonModel, appinfo, object, handler);
            }
        }
        map.put("commonModel", commonModel);
        map.put("isPermission", isPermission);

        return map;
    }
}
